<?php

namespace app\api\controller\datas;

use think\Config;
use think\Db;

/**
 * 查询
 * Class Querys
 * @package app\api\controller\datas
 * 
 * @method array excessCustomer($typs , $offset , $limit)  		超标客户
 * @method array lostCustomer($typs , $offset , $limit)			流失客户
 * @method array activeNotStands($typs , $offset , $limit)		激活未达标
 */
class Querys
{

	/**
	 * 实例对象
	 * @var object
	 */
	protected static $instance;

	/**
	 * 默认配置
	 * @var array
	 */
	protected $config = [
		"prefix" 		=> "fd_",
		"user"			=> "user",
		"act"			=> "terminal_actvie_stands",
		"losts"			=> "trans_lost_customer",
		"outs" 			=> "trans_limits_warn",
		"pro"			=> "product"
	];

	/**
	 * 实例化
	 * @var object
	 */
	public static function instance()
	{
		if (is_null(self::$instance)) {
			self::$instance = new static();
		}
		return self::$instance;
	}

	/**
	 * 超额客户
	 */
	public function excessCustomer($typs, $offset, $limit)
	{
		$ret = ["data" => [], "total" => 0];
		if ($typs) {
			$total = Db::name($this->config["outs"])->where("process", $typs)->count();
			if ($total) {
				$lists =  Db::name($this->config["outs"])
					->alias('a')
					->join("( select id ids,nickname,mobile allPhone,insert(mobile , 4 , 4 ,'****') hidPhone from " . $this->config["prefix"] . $this->config["user"] . " ) b", "a.uid=b.ids", "left")
					->field("id,nickname,allPhone,hidPhone,process,from_unixtime(process_time , '%Y-%m-%d') processTime,from_unixtime(times , '%Y-%m') times,limitVal,remark")
					->where("process", $typs)
					->limit((int)$offset, (int)$limit)
					->select();
				if ($lists) {
					foreach ($lists as $key => $vals) {
						$lists[$key]["processNick"] = $vals["process"] == 1 ? "已处理" : "未处理";
					}
				}
				$ret = ["data" => $lists, "total" => $total];
			}
		}
		return $ret;
	}

	/**
	 * 流失客户
	 */
	public function lostCustomer($typs, $offset, $limit)
	{
		$ret = ["data" => [], "total" => 0];
		if ($typs) {
			$now =  strtotime(date("Y-m-d", time()));
			/**
			 *	@todo 上线开启 时间条件
			 */
			// $where = "ends < " .  $now;
			$field = "*,";
			$field .= "UNIX_TIMESTAMP(FROM_UNIXTIME( last_time, '%Y-%m-%d' )) lasts,";
			$field .= "CAST( UNIX_TIMESTAMP( DATE_ADD( FROM_UNIXTIME( last_time, '%Y-%m-%d' ), INTERVAL termDays DAY ) ) AS UNSIGNED ) ends";
			$buildSql = Db::name($this->config["losts"])->field($field)->buildSql();
			$total =  Db::table($buildSql)
				->alias('a')
				// ->where($where)
				->where("process", $typs)
				->count();
			if ($total) {
				$lists = Db::table($buildSql)
					->alias('a')
					->join("( select id ids,nickname,mobile allPhone,insert(mobile , 4 , 4 ,'****') hidPhone from " . $this->config["prefix"] . $this->config["user"] . " ) b", "a.uid=b.ids", "left")
					->where("process", $typs)
					// ->where($where)
					->field("id,nickname,allPhone,hidPhone,process,from_unixtime(process_time , '%Y-%m-%d') processTime,termDays,from_unixtime(lasts , '%Y-%m-%d') lasts,from_unixtime(ends , '%Y-%m-%d') ends,stands_typs isStands,from_unixtime(create_time , '%Y-%m-%d') createTime,remark")
					->limit((int)$offset, (int) $limit)
					->select();
				if ($lists) {
					foreach ($lists as $key => $vals) {
						$lists[$key]["standsNick"] = $vals["isStands"] == 1 ? "未达标" : "已达标";
						$lists[$key]["processNick"] = $vals["process"] == 1 ? "已处理" : "未处理";
					}
				}
				$ret = ["data" => $lists, "total" => $total];
			}
		}
		return $ret;
	}

	/**
	 * 激活未达标
	 */
	public function activeNotStands($typs, $offset, $limit)
	{
		$ret = ["data" => [], "total" => 0];
		if ($typs) {
			$defaultWhere = ["stands_typs" => 1, "process" => $typs];
			$now =  strtotime(date("Y-m-d", time()));
			/**
			 *	@todo 上线开启 时间条件
			 */
			$timesWhere = "end_times < " . $now;
			$total = Db::name($this->config["act"])
				->where($defaultWhere)
				// ->where($timesWhere)
				->count();

			if ($total) {
				$lists = Db::name($this->config["act"])
					->alias('a')
					->join("( select id ids,nickname,mobile allPhone,insert(mobile , 4 , 4 ,'****') hidPhone from " . $this->config["prefix"] . $this->config["user"] . " ) b", "a.uid=b.ids", "left")
					// ->where($timesWhere)
					->where($defaultWhere)
					->field("id,proid,sn,termDays,from_unixtime(bind_times , '%Y-%m-%d') bindTime,from_unixtime(end_times , '%Y-%m-%d') endTime,stands_money stanAmt,money tranAmt,process,ifnull(process_time, from_unixtime(process_time , '%Y-%m-%d %H:%i:%s')) processTime,stands_typs isStands,nickname,allPhone,hidPhone,remark")
					->limit($offset, $limit)
					->select();
				if ($lists) {
					$pro = Db::name($this->config['pro'])->column("name", "id");
					foreach ($lists as $key => $vals) {
						$lists[$key]["proname"] = $pro[$vals["proid"]];
						$lists[$key]["standsNick"] = $vals["isStands"] == 1 ? "未达标" : "已达标";
						$lists[$key]["processNick"] = $vals["process"] == 1 ? "已处理" : "未处理";
					}
				}
				$ret = ["data" => $lists, "total" => $total];
			}
		}
		return $ret;
	}
}
